package com.hileto.inf.utils;

import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

/**
 * Copyright: Copyright (c) 2012
 *     深圳市海乐淘电子商务有限公司
 * @Author : 曾林宝
 * @CreateTime : 2013-1-21 上午10:44:45
 *
 * @Description: 数据库操作工具类.
 */
public class DBUtils {
	private static Log log = LogFactory.getLog(DBUtils.class);
	private static final int N = 100;

	public static String getHashTable(String table, long flag, int n) {
		if ((flag < 0L) || (n <= 0)) {
			return table;
		}

		long suffix = flag % n;

		int currentSuffixLength = Long.toString(suffix).length();
		int completeSuffixLength = Integer.toString(n).length();
		if ((Integer.parseInt(Integer.toString(n).substring(0, 1)) == 1)
				&& (Integer.parseInt(Integer.toString(n).substring(1)) == 0)) {
			completeSuffixLength--;
		}

		int lackSuffixLength = completeSuffixLength - currentSuffixLength;

		StringBuilder sb = new StringBuilder().append(table).append("_");
		for (int i = 0; i < lackSuffixLength; i++){
			sb.append("0");
		}
		sb.append(suffix);
		return sb.toString();
	}

	public static String getHashTable(String table, long flag) {
		return getHashTable(table, flag, N);
	}

	public static String getCountSql(String sql) {
		sql = sql.replaceAll("(^\\s*\\()|(\\)\\s*$)", "")
				.replaceAll("(?i)select\\s+", "select ")
				.replaceAll("(?i)from\\s+", "from ")
				.replaceAll("(?i)order\\s+by", "order by")
				.replaceAll("(?i)limit", "limit");

		int pointer = sql.indexOf("select", 0);

		LinkedList<String> stack = new LinkedList<String>();
		stack.push("select");
		while (!stack.isEmpty()) {
			String first = (String) stack.getFirst();
			int p1 = sql.indexOf("select", pointer + 4);
			int p2 = sql.indexOf("from", pointer + 4);
			p1 = p1 == -1 ? 100000 : p1;
			p2 = p2 == -1 ? 100000 : p2;
			if ((p1 == 100000) && (p2 == 100000))
				throw new RuntimeException("SQL语句非法...");
			if (p1 <= p2) {
				pointer = p1;
				if (first.equals("select"))
					stack.push("select");
				else
					stack.pop();
			} else {
				pointer = p2;
				if (first.equals("from"))
					stack.push("from");
				else
					stack.pop();
			}
		}
		int fromIndex = pointer;
		sql = sql.substring(fromIndex);
		int orderIndex = sql.lastIndexOf("order by");
		if ((orderIndex != -1) && (sql.indexOf(")", orderIndex) < 0)) {
			sql = sql.substring(0, orderIndex);
		}
		int limitIndex = sql.lastIndexOf("limit");
		if ((limitIndex != -1) && (sql.indexOf(")", limitIndex) < 0)) {
			sql = sql.substring(0, limitIndex);
		}
		return new StringBuilder().append("select count(1) count ").append(sql)
				.toString();
	}

	public static String restrictSelectSQL(String sql, Class<?> clazz) {
		return sql.replace("*", generateTargetFields(clazz));
	}

	public static String generateSelectSQL(String table,
			Map<String, Object> conditions, String conditionsExt) {
		if ((conditions == null) || (conditions.isEmpty())) {
			StringBuilder sball = new StringBuilder().append("select * from ")
			.append(table);
			return sball.toString();
		}
		
		StringBuilder sb = new StringBuilder().append("select * from ")
				.append(table).append(" where 1 = 1");

		for (Entry<String, Object> entry : conditions.entrySet()) {
			String key = entry.getKey();
			sb.append(" and ").append(key).append(" = ").append(":")
					.append(key);
		}

		if (ObjectHelp.isNotEmpty(conditionsExt)) {
			sb.append(" ").append(conditionsExt);
		}

		return sb.toString();
	}

	public static String generateSelectSQL(String table,
			Map<String, Object> conditions) {
		return generateSelectSQL(table, conditions, "");
	}

	public static <T> String generateInsertSQL(String table, T t,String... offsetField) {
		StringBuilder sb1 = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();
		sb1.append("insert into ").append(table).append(" (");
		sb2.append(") values (");

		Field[] fields = t.getClass().getDeclaredFields();
		String key = null;
		String fieldName = null;
		List<String> offsetFieldLst = new ArrayList<String>();
		Collections.addAll(offsetFieldLst, offsetField);
		for (Field field : fields) {
			fieldName = field.getName();
			if ("serialVersionUID".equals(fieldName) && offsetFieldLst.contains(fieldName)){
				continue;
			}
			sb1.append(fieldName).append(", ");
			key = ":" + fieldName;
			sb2.append(key).append(", ");
		}
		sb1.delete(sb1.lastIndexOf(","), sb1.length());
		sb2.delete(sb2.lastIndexOf(","), sb2.length());
		sb1.append(sb2).append(")");
		return sb1.toString();
	}

	public static String generateInsertSQL(String table,
			Map<String, Object> fields) {
		StringBuilder sb1 = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();
		sb1.append("insert into ").append(table).append(" (");
		sb2.append(" ) values (");

		for (Iterator<String> it = fields.keySet().iterator(); it.hasNext();) {
			String key = it.next();
			sb1.append(key);
			sb2.append(":").append(key);

			if (it.hasNext()) {
				sb1.append(", ");
				sb2.append(", ");
			}
		}

		sb1.append(sb2).append(")");

		return sb1.toString();
	}

	public static <T> String generateReplaceSQL(String table, T t) {
		StringBuilder sb1 = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();
		sb1.append("replace into ").append(table).append(" (");
		sb2.append(") values (");

		Field[] fields = t.getClass().getDeclaredFields();
		for (Field field : fields) {
			if ("serialVersionUID".equals(field.getName()))
				continue;
			sb1.append(field.getName()).append(", ");
			sb2.append(":").append(field.getName()).append(", ");
		}
		sb1.delete(sb1.lastIndexOf(","), sb1.length());
		sb2.delete(sb2.lastIndexOf(","), sb2.length());
		sb1.append(sb2).append(")");

		return sb1.toString();
	}

	public static String generateReplaceSQL(String table,
			Map<String, Object> fields) {
		StringBuilder sb1 = new StringBuilder();
		StringBuilder sb2 = new StringBuilder();
		sb1.append("replace into ").append(table).append(" (");
		sb2.append(") values (");

		for (Iterator<String> it = fields.keySet().iterator(); it.hasNext();) {
			String key = it.next();
			sb1.append(key);
			sb2.append(":").append(key);

			if (it.hasNext()) {
				sb1.append(", ");
				sb2.append(", ");
			}
		}

		sb1.append(sb2).append(")");

		return sb1.toString();
	}

	public static String generateUpdateSQL(String table,
			Map<String, Object> fields, Map<String, Object> conditions) {
		return generateUpdateSQL(table, fields, conditions, "");
	}

	public static String generateUpdateSQL(String table,
			Map<String, Object> fields, Map<String, Object> conditions,
			String conditionsExt) {
		if ((fields == null) || (fields.size() == 0)) {
			return null;
		}

		StringBuilder sb = new StringBuilder().append("update ").append(table)
				.append(" set ");
		for (Iterator<String> it = fields.keySet().iterator(); it.hasNext();) {
			String key = it.next();
			sb.append(key).append(" = ").append(":").append(key);
			if (it.hasNext()) {
				sb.append(", ");
			}
		}
		sb.append(" where 1 = 1");
		for (Iterator<String> it = conditions.keySet().iterator(); it.hasNext();) {
			String key = it.next();
			sb.append(" and ").append(key).append(" = ").append(":")
					.append(key);
		}

		if (!ObjectHelp.isEmpty(conditionsExt)) {
			sb.append(" ").append(conditionsExt);
		}

		return sb.toString();
	}

	public static String generateDeleteSQL(String table,
			Map<String, Object> conditions) {
		return generateDeleteSQL(table, conditions, "");
	}

	public static String generateDeleteSQL(String table,
			Map<String, Object> conditions, String conditionsExt) {
		if ((conditions == null) || (conditions.size() == 0)) {
			return null;
		}

		StringBuilder sb = new StringBuilder().append("delete from ")
				.append(table).append(" where 1 = 1");
		for (Iterator<String> it = conditions.keySet().iterator(); it.hasNext();) {
			String key = it.next();
			sb.append(" and ").append(key).append(" = ").append(":")
					.append(key);
		}

		if (ObjectHelp.isNotEmpty(conditionsExt)) {
			sb.append(" ").append(conditionsExt);
		}

		return sb.toString();
	}

	public static String generateCountSQL(String table,
			Map<String, Object> conditions) {
		StringBuilder sb = new StringBuilder().append("select count(1) from ")
				.append(table).append(" where 1 = 1");
		Iterator<String> it;
		if ((conditions != null) && (conditions.size() != 0)) {
			for (it = conditions.keySet().iterator(); it.hasNext();) {
				String key = it.next();
				sb.append(" and ").append(key).append(" = ").append(":")
						.append(key);
			}
		}
		sb.append(" limit 1");

		return sb.toString();
	}

	public static String generateTargetFields(Class<?> clazz) {
		StringBuilder sb = new StringBuilder();

		Field[] fields = clazz.getDeclaredFields();
		for (Field field : fields) {
			if ("serialVersionUID".equals(field.getName()))
				continue;
			sb.append(new StringBuilder().append(field.getName()).append(", ")
					.toString());
		}
		sb.delete(sb.lastIndexOf(","), sb.length());

		return sb.toString();
	}

	@SuppressWarnings("all")
	public static Map<String, Object> comparePojo(Object src, Object dest) {
		if ((dest == null)
				|| ((src != null) && (!dest.getClass().equals(src.getClass())))) {
			return null;
		}

		Map<String, Object> values = new HashMap<String, Object>();
		Field[] fields = dest.getClass().getDeclaredFields();
		for (Field field : fields) {
			String name = field.getName();
			Object value = null;

			if (!"serialVersionUID".equals(name)) {
				Object v1 = null;
				Object v2 = null;
				try {
					String methodPrefix = Boolean.TYPE.equals(field.getType()) ? "is"
							: "get";
					Method m = dest.getClass()
							.getMethod(
									new StringBuilder(methodPrefix).append(
											getFirstUpperString(name))
											.toString(), null);
					v1 = m.invoke(dest, null);
					if (src != null)
						v2 = m.invoke(src, null);
				} catch (Exception e) {
					log.error(new StringBuilder().append(e).append(" : name=")
							.append(name).append(", value=").append(value)
							.toString(), e);
				}
				if ((v1 != null) && (!v1.equals(v2))) {
					values.put(name, v1);
				}
			}
		}
		return values;
	}

	public static String getFirstUpperString(String s) {
		if ((s == null) || (s.length() < 1)) {
			return "";
		}
		return new StringBuilder().append(s.substring(0, 1).toUpperCase())
				.append(s.substring(1)).toString();
	}
}